<?php
class DataTable
{
	protected $tableName;
	protected $primaryKey = 'id';
	protected $primaryKeyAuto = true;
	protected $column_list;
	private $result;
	public $TrimFieldValue = true;
	public $DEBUG_LEVEL = 0;
	private static $SHOWING_SQL = 1;
	
	public $query_value_for_html_show = true;
	public $primaryKeyAsKey = false;
	
	public function getPrimaryKey()
	{
		return $this->primaryKey;
	}
	public function DataTable($tableName,$primaryKey = NULL,$primaryKeyAuto = true)
	{
			$this->tableName = $tableName;
            if(!empty($primaryKey))
            {
                $this->primaryKey = $primaryKey;
            }
            $this->primaryKeyAuto = $primaryKeyAuto;
                
		if(!is_array($this->column_list)){
			//try to get table structure info from cache saved in $_REQUEST
			if(isset($_REQUEST['__DATABASE_TABLE_STRUCTURE_INFO__']) && isset($_REQUEST['__DATABASE_TABLE_STRUCTURE_INFO__'][$this->tableName]))
			{
				$copy = $_REQUEST['__DATABASE_TABLE_STRUCTURE_INFO__'][$this->tableName];
				$this->tableName = $copy->tableName;
				$this->primaryKey = $copy->primaryKey;
				$this->primaryKeyAuto = $copy->primaryKeyAuto;
				$this->column_list = $copy->column_list;
			}
			else
			{
				$this->column_list = array();
				$this->reflect_table_structure();
				//cache table structure to $_REQUEST,
				$_REQUEST['__DATABASE_TABLE_STRUCTURE_INFO__'][$this->tableName] = $this;	
			}
		}
	}
	public function get_list($condition='',$sortBy='',$limit=5000,$offset=0)
	{
		if(!is_array($sortBy)){
			$sortType = 'ASC';
			if(substr($sortBy, 0, 1) == '-'){
				$sortBy = substr($sortBy,1);
				$sortType = 'DESC';
			}
			if(!empty($sortBy))
				$sortBy = sprintf(" ORDER BY `%s` %s",$sortBy,$sortType);
		}else{
			$sortString = " ORDER BY ";
			$first = true;
			foreach($sortBy as $sortField){
				$sortType = 'ASC';
				if(substr($sortField, 0, 1) == '-'){
					$sortField = substr($sortField,1);
					$sortType = 'DESC';
				}
				$sortString .= ($first?'':',').sprintf(" `%s` %s", $sortField, $sortType);
				$first = false;
			}
			$sortBy = $sortString;
		}
		if(!empty($condition))
			$condition = sprintf("WHERE %s",$condition);
		
		$query = sprintf("SELECT * FROM %s %s %s", $this->tableName,$condition, $sortBy);
		if(intval($limit) > 0 && intval($offset) > -1)
			$query .= sprintf(" LIMIT %d ,%d",$offset,$limit);

		$this->_execute_query($query);
		$list = array();
		if($this->result->result != null)
		{
			while ( ($row = $this->result->fetch_assoc()) )
			{
				if($this->query_value_for_html_show)
				{
					foreach($row as $tmpKey=>$tmpValue)
					{
						$row[$tmpKey] = $this->html_safe($tmpValue);
					}
				}
				if($this->primaryKeyAsKey)
				{
					$list[$row[$this->primaryKey]] = $row;
				}else{
					array_push($list, $row);
				}
			}
		}
		return $list;
	}
	public function get_row($condition='',$sortBy='',$limit=5000,$offset=0)
	{
		$list = $this->get_list($condition,$sortBy,$limit,$offset);
		$row = null;
		if(count($list) > 0)
		{
			$row = $list[0];
		}
		return $row;
	}
	public function get_list_by_sql($sql,$sortBy='',$limit=5000,$offset=0)
	{
		$sortType = 'ASC';
		if(substr($sortBy, 0, 1) == '-'){
			$sortBy = substr($sortBy,1);
			$sortType = 'DESC';
		}
		if(!empty($sortBy))
			$sortBy = sprintf(" ORDER BY `%s` %s",$sortBy,$sortType);
		$query = sprintf("%s %s LIMIT %d ,%d", $sql, $sortBy,$offset,$limit);
		$this->_execute_query($query);
		$list = array();
		if($this->result->result != null)
		{
			while ( ($row = $this->result->fetch_assoc()) )
			{
				if($this->query_value_for_html_show)
				{
					foreach($row as $tmpKey=>$tmpValue)
					{
						$row[$tmpKey] = $this->html_safe($tmpValue);
					}
				}
				array_push($list, $row);
				}
		}
		return $list;		
	}

	public function get_instance_by_sql($sql)
	{
		$data_return = $this->get_list_by_sql($sql,'',1);
		if(count($data_return) == 1)
		{
			return $data_return[0];
		}
		return null;
	}
	public function get_list_sortby($sortBy)
	{
		$i = 0;
		$sortby_list = array();
		foreach($this->column_list as $column)
		{
			if($this->sortby_fileter($column)){
				if($column == $sortBy){
					$sortby_list[$column] = "-".$column;
				}else{
					$sortby_list[$column] = $column;
				}
				
			}
		}
		return $sortby_list;
	}
	protected function sortby_fileter($column)
	{
		return true;
	}
	public function get_count($condition = '')
	{
		if(!empty($condition))
			$condition = sprintf("WHERE %s",$condition);
					
		$query = sprintf("SELECT count(1) AS `count` FROM %s %s",$this->tableName,$condition);
		$this->_execute_query($query);
		if($this->result->result != null)
		{
			if($this->result->result != null)
			{
				$row = $this->result->fetch_array();
				return $row['count'];
			}
		}
		return 0;
	}
	public function get_count_by_sql($sql)
	{
		$this->_execute_query($sql);
		if($this->result->result != null)
		{
			if($this->result->result != null)
			{
				$row = $this->result->fetch_array();
				return $row[0];
			}
		}
		return 0;		
	}
	public function get_instance_by_primaryKey($primaryKey)
	{
		$query = sprintf("SELECT * FROM %s WHERE `%s`='%s'",$this->tableName,$this->primaryKey, $primaryKey);
		$this->_execute_query($query);
		if($this->result->result != null)
		{
			$row = $this->result->fetch_assoc();
			if($row === FALSE)
			{
				return NULL;
			}	
		}
	 if($this->query_value_for_html_show)
		{
			foreach($row as $tmpKey=>$tmpValue)
			{
				$row[$tmpKey] = $this->html_safe($tmpValue);
			}
		}
		return $row;
	}
	/**
	 * 
	 *Short name for get_instance_by_primaryKey function
	 */
	public function get($primaryKey)
	{
		return $this->get_instance_by_primaryKey($primaryKey);
	}
	public function get_instance_by_condition($condition)
	{
		$list = $this->get_list($condition,NULL,1);
		return count($list)>0?$list[0]:NULL;
	}
	//short name for removeByPrimaryKey method
	public function remove($primaryKey,$do_not_quit_on_error=false,&$error=null)
	{
		$this->removeByPrimaryKey($primaryKey,$do_not_quit_on_error,$error);
	}
	public function removeByPrimaryKey($primaryKey)
	{
		$query = sprintf("DELETE FROM %s WHERE `%s`='%s'",$this->tableName,$this->primaryKey, $primaryKey);
		$this->_execute_query($query,$affected_rows);
		if($affected_rows != 1)
			$error = "record '".$primaryKey."' not exist";
		return true;
	}
	public function removeByCondition($condition)
	{
		$query = sprintf("DELETE FROM %s WHERE %s",$this->tableName,$condition);
		$this->_execute_query($query);
	}
	public function removeAll($truncate=false){
		if(!$truncate){	
			$query = sprintf("DELETE FROM %s",$this->tableName);
		}else{
			$query = sprintf("TRUNCATE TABLE %s",$this->tableName);
		}
		$this->_execute_query($query);
	}
	public function save(&$data,$create_if_possible=true){
		$need_to_update_primary_key = false;
		$query = null;
		$error = false;
		if(array_key_exists($this->primaryKey,$data) && !empty($data[$this->primaryKey])){
		    //check record exist
		    $tmpRow = $this->get($data[$this->primaryKey]);
		    if($tmpRow == null)
		    {
		    	throw new Exception("can not find record ".$data[$this->primaryKey]);
		    }
			$query = $this->_build_update_query($data);
		}else if($create_if_possible){
			$query = $this->_build_insert_query($data);
			if($this->primaryKeyAuto){
				$need_to_update_primary_key = true;
			}
		}else{
			$error = "data was not saved";
		}
		if($query){
			$this->_execute_query($query,$affected_rows);
			if($need_to_update_primary_key){
				$data[$this->primaryKey] = mysql_insert_id();
			}else if(!$error){
				$error = $affected_rows == 1 ? "" : "data was not saved";
			}
		}
		return $data;
	}
	public function insert(&$data,$ignore_primary_key=false)
	{
		$query = $this->_build_insert_query($data,false,$ignore_primary_key);
		$this->_execute_query($query);
		if($ignore_primary_key)
			$data[$this->primaryKey] = mysql_insert_id();
		return $data;
	}
	public function update(&$data)
	{
		//check record exist
		$tmpRow = $this->get($data[$this->primaryKey]);
	    if($tmpRow == null)
		{
		    throw new Exception("can not find record ".$data[$this->primaryKey]);
		}
		    
		$query = $this->_build_update_query($data);
		$this->_execute_query($query);
		return $data;
	}
	public function updateByCondition($data,$condition)
	{
		$set = '';
		foreach ($this->column_list as $key)
		{
			if($this->primaryKeyAuto && $key == $this->primaryKey) continue;
			if (array_key_exists($key, $data)){
				if (is_null($data[$key]))
					$set .= sprintf("`%s`=NULL, ", $key, $this->safe_string($data[$key]));
				else
					$set .= sprintf("`%s`='%s', ", $key, $this->safe_string($data[$key]));
			}
		}
		
		$set = trim($set, ', ');
		$query = sprintf("UPDATE %s SET %s WHERE %s",$this->tableName, $set,$condition);
		$this->_execute_query($query);
	}
	public function replace($data)
	{
		//check record exist
		$tmpRow = $this->get($data[$this->primaryKey]);
		if($tmpRow == null)
		{
		    throw new Exception("can not find record ".$data[$this->primaryKey]);
		}
		$query = $this->_build_replace_query($data);
		$this->_execute_query($query);
		if(!array_key_exists($this->primaryKey,$data) || empty($data[$this->primaryKey]))
		{
			if($this->primaryKeyAuto)
			{
				$data[$this->primaryKey] = mysql_insert_id();
			}
		}
		
		 
		return $data;	
	}

	private function _build_insert_query($data,$replace_mdoe=false,$ignore_primary_key=true)
	{
	
		$set = '';
		foreach ($this->column_list as $key)
		{
			if($this->primaryKeyAuto && $key == $this->primaryKey && $ignore_primary_key) continue;
			if (array_key_exists($key, $data)){
				if (is_null($data[$key]))
					$set .= sprintf("`%s`=NULL, ", $key, $this->safe_string($data[$key]));
				else
					$set .= sprintf("`%s`='%s', ", $key, $this->safe_string($data[$key]));
			}
		}
					
		$set = trim($set, ', ');
	
		// perform the insert
		if($replace_mdoe){
			$query = sprintf("REPLACE INTO %s SET %s",$this->tableName, $set);
		}else{
			$query = sprintf("INSERT INTO %s SET %s",$this->tableName, $set);
		}
		return $query;
	}
	private function _build_update_query($data)
	{
		$set = '';
		foreach ($this->column_list as $key)
		{
			if($this->primaryKeyAuto && $key == $this->primaryKey) continue;
			if (array_key_exists($key, $data)){
				if (is_null($data[$key]))
					$set .= sprintf("`%s`=NULL, ", $key, $this->safe_string($data[$key]));
				else
					$set .= sprintf("`%s`='%s', ", $key, $this->safe_string($data[$key]));
			}
		}
		
		$set = trim($set, ', ');

		// perform the insert
		$query = sprintf("UPDATE %s SET %s WHERE %s='%s'",$this->tableName, $set,$this->primaryKey,$data[$this->primaryKey]);
		return $query;	
	}
	private function _build_replace_query($data)
	{
		$set = '';
		foreach ($this->column_list as $key)
		{
			if (array_key_exists($key, $data))
				$set .= sprintf("`%s`='%s', ", $key, $this->safe_string($data[$key]));
		}
		
		$set = trim($set, ', ');
		// perform the replace
		$query = sprintf("REPLACE INTO %s SET %s",$this->tableName, $set);
		return $query;	
		
	}
	protected function _execute_query($query,&$affected_rows=null){
		if($this->DEBUG_LEVEL >= DataTable::$SHOWING_SQL)
			echo sprintf("Executing SQL:%s<br>",$query);
		$this->result = new DATATABLE_DB_DRIVER();
		$affected_rows = $this->result->query_affected_rows($query);
		if ($this->result->result == FALSE && $do_not_quit_on_error == false){
			if($this->DEBUG_LEVEL > 0)
			{
				throw new Exception(mysql_error().' sql:'.$query);
			}else{
				throw new Exception(mysql_error());
			}
			
		}
		
	}
	public function get_column_list()
	{
		return $this->column_list;
	}
	public function get_primaryKey()
	{
		return $this->primaryKey;
	}
	
	private function reflect_table_structure()
	{
		$query = sprintf("DESC %s",$this->tableName);
		$result = $this->_execute_query($query);
		$table_column_list = array();
		if($this->result->result != null)
		{
			while ( ($row = $this->result->fetch_assoc()) )
				array_push($table_column_list,$row);
		}

			
		foreach($table_column_list as $table_column )
		{
			array_push($this->column_list,$table_column['Field']);
			if($table_column['Key'] == 'PRI')
			{
				$this->primaryKey = $table_column['Field'];
				if($table_column['Extra'] == "auto_increment")
				{
					$this->primaryKeyAuto = true;
				}
			}
		}
	}
	public function describe_column($name){
		$query = sprintf("DESC %s `%s`",$this->tableName,$name);
		$result = $this->_execute_query($query);
		if(null != $this->result->result){
			return $this->result->fetch_assoc(); 
		}
		return null;
	}
	public function column_default_value($name){
		$column = $this->describe_column($name);
		if($column){
			if(!is_null($column['Default'])){
				return $column['Default'];
			}
			$canBeNull = $column['Null'] == 'YES';
			if(!$canBeNull){
				if(preg_match('~char|tinytext|text|mediumtext|longtext|blob|set~',$column['Type'])){
					return '';
				}else if(preg_match('~int|decimal|float|double~',$column['Type'])){
					return 0;
				}else if(preg_match('~enum~',$column['Type']) && !$canBeNull){
					$values = str_replace('enum(', '', $column['Type']);
					$values = str_replace(')', '', $values);
					$values = explode(',', $values);
					if(count($values)){
						return $values[0];
					}
				}
			}
			return $column['Default'];
		}
		return null;
	}
	public function clonInstance($data)
	{
		if(!is_array($data))
		{
			return null;
		}
		$clon_data = array();
		foreach($this->column_list as $key)
		{
			if(array_key_exists($key,$data))
			{
				$clon_data[$key] = $data[$key];
			}
			else
			{
				$clon_data[$key] = '';
			}
		}
		return $clon_data;
	}
	
	private function safe_string($str)
	{
		 if (get_magic_quotes_gpc())
	  		 return $str;
		 $value = addslashes($str);
		 if($this->TrimFieldValue)
		 {
		 	 $value = trim($value);
		 }
		 return $value;
	}
	 private  function  html_safe($str,$encode = 'UTF-8')
	 {
 		return htmlentities($str,ENT_COMPAT,$encode);
 	}
}
?>
